{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw ,os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "跑腿\n",
      "家政\n",
      "代驾\n",
      "头盔\n",
      "库存\n"
     ]
    }
   ],
   "source": [
    "# 打开工作簿\n",
    "工作簿路径=R'D:\\BaiduSyncdisk\\王振洋资料\\办公微信\\8月物料统计表.xlsx'\n",
    "\n",
    "wb8月=xw.Book(工作簿路径) #8月物料统计表 \n",
    "\n",
    "for i in wb8月.sheets:\n",
    "    print(i.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理合并单元格，将其解散并在原合并区域中填充值\n",
    "\n",
    "import xlwings as xw\n",
    "# 打开 Excel 文件\n",
    "wb = xw.Book(工作簿路径)\n",
    "sheet = wb.sheets['跑腿 (2)']\n",
    "\n",
    "# 选择要检查的列\n",
    "column = 'b'\n",
    "\n",
    "# 获取列的范围\n",
    "column_range = sheet.range(f'{column}1:{column}1000')\n",
    "\n",
    "# 遍历列中的每个单元格\n",
    "# 遍历列中的每个单元格\n",
    "for cell in column_range:\n",
    "    if cell.api.MergeCells:   #判断是否是合并单元格\n",
    "        # 获取合并单元格区域的范围\n",
    "        merged_range = cell.api.MergeArea.Address\n",
    "        # 解散合并单元格\n",
    "        cell.api.UnMerge()\n",
    "        # 填充合并区域内的所有单元格\n",
    "        sheet.range(merged_range).value = cell.value\n",
    "      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\anaconda\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n",
      "d:\\anaconda\\Lib\\site-packages\\openpyxl\\worksheet\\header_footer.py:48: UserWarning: Cannot parse header or footer so it will be ignored\n",
      "  warn(\"\"\"Cannot parse header or footer so it will be ignored\"\"\")\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "dict_keys(['跑腿', '跑腿 (2)', '家政', '代驾', '头盔', '库存'])"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据\n",
    "a=pd.read_excel(工作簿路径,sheet_name=None) # sheet_name=None时，将读取所有工作表的数据，形成一个字典，键是工作表的名称 使用a['key']可以取出对应的dataframe\n",
    "a.keys()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取数据\n",
    "df=a['跑腿 (2)'] \n",
    "\n",
    "# 清洗数据\n",
    "\n",
    "#1.将第一行提升为标题\n",
    "df.columns=df.iloc[0]\n",
    "df1=df[1:]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2.看看有没有na行，数量，单价，总价这三列\n",
    "\n",
    "filtered_df1 = df[(df['数量'] == 0) | (df['数量'].isnull())]\n",
    "filtered_df2 = df[(df['单价'] == 0) | (df['单价'].isnull())]\n",
    "filtered_df3 = df[(df['总价'] == 0) | (df['总价'].isnull())]\n",
    "\n",
    "xw.view(filtered_df1)\n",
    "xw.view(filtered_df2)\n",
    "xw.view(filtered_df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2.将日期列转换为时间格式\n",
    "df1['日期']=pd.to_datetime(df1['日期'],origin='1899-12-30', unit='D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "File \u001b[1;32md:\\anaconda\\Lib\\site-packages\\xlwings\\_xlwindows.py:186\u001b[0m, in \u001b[0;36mCOMRetryObjectWrapper.__getattr__\u001b[1;34m(self, item)\u001b[0m\n\u001b[0;32m    185\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m--> 186\u001b[0m     v \u001b[39m=\u001b[39m \u001b[39mgetattr\u001b[39m(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_inner, item)\n\u001b[0;32m    187\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39misinstance\u001b[39m(v, (CDispatch, CoClassBaseClass, DispatchBaseClass)):\n",
      "File \u001b[1;32md:\\anaconda\\Lib\\site-packages\\win32com\\client\\__init__.py:580\u001b[0m, in \u001b[0;36mDispatchBaseClass.__getattr__\u001b[1;34m(self, attr)\u001b[0m\n\u001b[0;32m    579\u001b[0m \u001b[39mif\u001b[39;00m args \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[1;32m--> 580\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mAttributeError\u001b[39;00m(\n\u001b[0;32m    581\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39m'\u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m'\u001b[39m\u001b[39m object has no attribute \u001b[39m\u001b[39m'\u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m'\u001b[39m\u001b[39m\"\u001b[39m \u001b[39m%\u001b[39m (\u001b[39mrepr\u001b[39m(\u001b[39mself\u001b[39m), attr)\n\u001b[0;32m    582\u001b[0m     )\n\u001b[0;32m    583\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_ApplyTypes_(\u001b[39m*\u001b[39margs)\n",
      "\u001b[1;31mAttributeError\u001b[0m: '<win32com.gen_py.Microsoft Excel 16.0 Object Library.Range instance at 0x2537054510416>' object has no attribute 'Autofilter'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32md:\\BaiduSyncdisk\\Ajupy\\2.23日pandas学习与尝试\\供应商发货明细与销售台账核对.ipynb 单元格 8\u001b[0m line \u001b[0;36m2\n\u001b[0;32m      <a href='vscode-notebook-cell:/d%3A/BaiduSyncdisk/Ajupy/2.23%E6%97%A5pandas%E5%AD%A6%E4%B9%A0%E4%B8%8E%E5%B0%9D%E8%AF%95/%E4%BE%9B%E5%BA%94%E5%95%86%E5%8F%91%E8%B4%A7%E6%98%8E%E7%BB%86%E4%B8%8E%E9%94%80%E5%94%AE%E5%8F%B0%E8%B4%A6%E6%A0%B8%E5%AF%B9.ipynb#X12sZmlsZQ%3D%3D?line=0'>1</a>\u001b[0m \u001b[39m# excel的自动筛选\u001b[39;00m\n\u001b[1;32m----> <a href='vscode-notebook-cell:/d%3A/BaiduSyncdisk/Ajupy/2.23%E6%97%A5pandas%E5%AD%A6%E4%B9%A0%E4%B8%8E%E5%B0%9D%E8%AF%95/%E4%BE%9B%E5%BA%94%E5%95%86%E5%8F%91%E8%B4%A7%E6%98%8E%E7%BB%86%E4%B8%8E%E9%94%80%E5%94%AE%E5%8F%B0%E8%B4%A6%E6%A0%B8%E5%AF%B9.ipynb#X12sZmlsZQ%3D%3D?line=1'>2</a>\u001b[0m wb8月\u001b[39m.\u001b[39msheets(\u001b[39m'\u001b[39m\u001b[39m跑腿 (2)\u001b[39m\u001b[39m'\u001b[39m)\u001b[39m.\u001b[39mrange(\u001b[39m'\u001b[39m\u001b[39ma2\u001b[39m\u001b[39m'\u001b[39m)\u001b[39m.\u001b[39mapi\u001b[39m.\u001b[39mAutofilter\n",
      "File \u001b[1;32md:\\anaconda\\Lib\\site-packages\\xlwings\\_xlwindows.py:208\u001b[0m, in \u001b[0;36mCOMRetryObjectWrapper.__getattr__\u001b[1;34m(self, item)\u001b[0m\n\u001b[0;32m    201\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mAttributeError\u001b[39;00m:\n\u001b[0;32m    202\u001b[0m     \u001b[39m# pywin32 reacts incorrectly to RPC_E_CALL_REJECTED (i.e. assumes\u001b[39;00m\n\u001b[0;32m    203\u001b[0m     \u001b[39m# attribute doesn't exist, thus not allowing to distinguish between\u001b[39;00m\n\u001b[0;32m    204\u001b[0m     \u001b[39m# cases where attribute really doesn't exist or error is only being\u001b[39;00m\n\u001b[0;32m    205\u001b[0m     \u001b[39m# thrown because the COM RPC server is busy). Here we try to test to\u001b[39;00m\n\u001b[0;32m    206\u001b[0m     \u001b[39m# see what's going on really\u001b[39;00m\n\u001b[0;32m    207\u001b[0m     \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m--> 208\u001b[0m         \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_oleobj_\u001b[39m.\u001b[39mGetIDsOfNames(\u001b[39m0\u001b[39m, item)\n\u001b[0;32m    209\u001b[0m     \u001b[39mexcept\u001b[39;00m pythoncom\u001b[39m.\u001b[39mole_error \u001b[39mas\u001b[39;00m e:\n\u001b[0;32m    210\u001b[0m         \u001b[39mif\u001b[39;00m e\u001b[39m.\u001b[39mhresult \u001b[39m!=\u001b[39m \u001b[39m-\u001b[39m\u001b[39m2147418111\u001b[39m:  \u001b[39m# RPC_E_CALL_REJECTED\u001b[39;00m\n\u001b[0;32m    211\u001b[0m             \u001b[39m# attribute probably really doesn't exist\u001b[39;00m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
